package org.languageguide.readings;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.languageguide.storage.DBConnector;

public class PublishedReading {
	
	private String title;
	private String text;
	//private String soundFile;
	private String targetLanguage;
	private String id;
	private String times;
	
	public PublishedReading(String id, String targetLanguage){
		this.id = id;
		this.targetLanguage = targetLanguage;
		//String sqlLoad = "SELECT PublishedText FROM TranslatedReading WHERE TranslatedReading.TargetLanguage = \"" + targetLanguage + "\" AND Readings.Id = \"" + id + "\"";
		loadReadingFromDb();
	}
	
	private void loadReadingFromDb(){
		String sql_load = "SELECT Title, Times, TranslatedReading.PublishedText FROM Readings LEFT JOIN TranslatedReading ON Readings.Id = TranslatedReading.ReadingId WHERE Readings.Id = ? AND TranslatedReading.TargetLanguage = ?";	
		//System.out.println("sql_load:" + sql_load);
		DBConnector connector = DBConnector.getInstance();
		Connection connection = connector.getConnection();
		try {
			PreparedStatement ps = connection.prepareStatement(sql_load);
			ps.setString(1, id);
			ps.setString(2, targetLanguage);
			ResultSet set = ps.executeQuery();
			if (set.next()){
				processRow(set);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} 
		finally{
			connector.close(connection);
		}
	}
	
	private void processRow(ResultSet set) throws SQLException{
		title = set.getString("Readings.Title");
		text = set.getString("TranslatedReading.PublishedText");
		times = set.getString("Readings.Times");
		//soundFile = set.getString("Readings.SoundFile");
	}

	//public String getSoundFile(){
		//return soundFile;
	//}

	public String getTitle(){
		return title;
	}
	
	public String getTimes(){
		return times;
	}
	
	public String getText(){
		return text;
	}
}